package com.egao.orderB.biz.impl;

import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.ApplyCustomsBiz;
import com.egao.orderB.entity.ApplyCustoms;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2021-07-21 14:58:25
 */
@Repository
public class ApplyCustomsBizImpl extends BaseDao implements ApplyCustomsBiz {

  @Override
  public JsonResult list(PageResult2<T> page, ApplyCustoms applyCustoms, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<ApplyCustoms> rm = new BeanPropertyRowMapper<ApplyCustoms>(ApplyCustoms.class);
    StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.customer_id");
    sql.append(",a.customer,a.customs_broker,a.bill_of_lading,a.invoice_file,a.package_file,a.custom_file");
    sql.append(",a.contract_file,a.where_apply,a.release_permit,a.customs_date,a.create_time,a.create_user,a.update_time");
    sql.append(",a.update_user");
    sql.append(",a.payable,a.receivable");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(",boa.bill_id,boa.ocean_id");
    sql.append(" from chf_apply_customs a ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" left join chf_bill_ocean_apply boa on boa.apply_id=a.id ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=0 ");
    if (Utils.isNotEmpty(applyCustoms)) {
			if(Utils.isNotEmpty(applyCustoms.getCustomer())){
				sql.append(" and instr(a.customer,:customer)>0 ");
				map.put("customer", applyCustoms.getCustomer());
			}
			if(Utils.isNotEmpty(applyCustoms.getCustomsBroker())){
				sql.append(" and instr(a.customs_broker,:customs_broker)>0 ");
				map.put("customs_broker", applyCustoms.getCustomer());
			}
			if(Utils.isNotEmpty(applyCustoms.getBillOfLading())){
				sql.append(" and instr(a.bill_of_lading,:bill_of_lading)>0 ");
				map.put("bill_of_lading", applyCustoms.getBillOfLading());
			}
			if(Utils.isNotEmpty(applyCustoms.getDate1())){
				sql.append(" and a.customs_date>=:date1 ");
				map.put("date1", applyCustoms.getDate1());
			}
			if(Utils.isNotEmpty(applyCustoms.getDate2())){
				sql.append(" and a.customs_date<=:date2 ");
				map.put("date2", applyCustoms.getDate2());
			}
      if (Utils.isNotEmpty(applyCustoms.getSort()) && Utils.isNotEmpty(applyCustoms.getOrder())) {
        sql.append(" order by a." + applyCustoms.getSort() + " " + applyCustoms.getOrder());
      } else {
        sql.append(" order by a.create_time desc");
      }
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

  @Override
  public JsonResult listDel(PageResult2<T> page, ApplyCustoms applyCustoms, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<ApplyCustoms> rm = new BeanPropertyRowMapper<ApplyCustoms>(ApplyCustoms.class);
    StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.customer_id");
    sql.append(",a.customer,a.customs_broker,a.bill_of_lading,a.invoice_file,a.package_file,a.custom_file");
    sql.append(",a.contract_file,a.where_apply,a.release_permit,a.customs_date,a.create_time,a.create_user,a.update_time");
    sql.append(",a.update_user");
    sql.append(",a.payable,a.receivable");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(",boa.bill_id,boa.ocean_id");
    sql.append(" from chf_apply_customs a ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" left join chf_bill_ocean_apply boa on boa.apply_id=a.id ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=1 ");
    if (Utils.isNotEmpty(applyCustoms)) {
			if(Utils.isNotEmpty(applyCustoms.getCustomer())){
				sql.append(" and instr(a.customer,:customer)>0 ");
				map.put("customer", applyCustoms.getCustomer());
			}
			if(Utils.isNotEmpty(applyCustoms.getCustomsBroker())){
				sql.append(" and instr(a.customs_broker,:customs_broker)>0 ");
				map.put("customs_broker", applyCustoms.getCustomer());
			}
			if(Utils.isNotEmpty(applyCustoms.getBillOfLading())){
				sql.append(" and instr(a.bill_of_lading,:bill_of_lading)>0 ");
				map.put("bill_of_lading", applyCustoms.getBillOfLading());
			}
			if(Utils.isNotEmpty(applyCustoms.getDate1())){
				sql.append(" and a.customs_date>=:date1 ");
				map.put("date1", applyCustoms.getDate1());
			}
			if(Utils.isNotEmpty(applyCustoms.getDate2())){
				sql.append(" and a.customs_date<=:date2 ");
				map.put("date2", applyCustoms.getDate2());
			}
      if (Utils.isNotEmpty(applyCustoms.getSort()) && Utils.isNotEmpty(applyCustoms.getOrder())) {
        sql.append(" order by a." + applyCustoms.getSort() + " " + applyCustoms.getOrder());
      } else {
        sql.append(" order by a.customs_date desc");
      }
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

  @Override
  public List<ApplyCustoms> list(ApplyCustoms applyCustoms, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<ApplyCustoms> rm = new BeanPropertyRowMapper<ApplyCustoms>(ApplyCustoms.class);
    StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.customer_id");
    sql.append(",a.customer,a.customs_broker,a.bill_of_lading,a.invoice_file,a.package_file,a.custom_file");
    sql.append(",a.contract_file,a.where_apply,a.release_permit,a.customs_date,a.create_time,a.create_user,a.update_time");
    sql.append(",a.update_user");
    sql.append(",cb.box_type,cb.box_num,suser3.nick_name operaterName ");
    sql.append(",a.payable,a.receivable");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_apply_customs a ");
    sql.append(" left join chf_bill_ocean_apply boa on boa.apply_id=a.id ");
    sql.append(" left join chf_bill cb on boa.bill_id=cb.id ");
    sql.append(" left join sys_user suser3 on suser3.user_id=cb.operaterId ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=0 ");
    if (Utils.isNotEmpty(applyCustoms)) {
      if(Utils.isNotEmpty(applyCustoms.getCustomer())){
        sql.append(" and instr(a.customer,:customer)>0 ");
        map.put("customer", applyCustoms.getCustomer());
      }
      if(Utils.isNotEmpty(applyCustoms.getCustomsBroker())){
        sql.append(" and instr(a.customs_broker,:customs_broker)>0 ");
        map.put("customs_broker", applyCustoms.getCustomer());
      }
      if(Utils.isNotEmpty(applyCustoms.getDate1())){
        sql.append(" and a.customs_date>=STR_TO_DATE(:date1,'%Y-%m-%d %H:%i:%s') ");
        map.put("date1", applyCustoms.getDate1());
      }
      if(Utils.isNotEmpty(applyCustoms.getDate2())){
        sql.append(" and a.customs_date<=STR_TO_DATE(:date2,'%Y-%m-%d %H:%i:%s') ");
        map.put("date2", applyCustoms.getDate2());
      }
      if (Utils.isNotEmpty(applyCustoms.getSort()) && Utils.isNotEmpty(applyCustoms.getOrder())) {
        sql.append(" order by a." + applyCustoms.getSort() + " " + applyCustoms.getOrder());
      } else if(Utils.isNotEmpty(applyCustoms.getSort())){
        sql.append(" order by a.customs_date asc");
      } else {
        sql.append(" order by a.customs_date desc");
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public ApplyCustoms getById(ApplyCustoms applyCustoms) {
    RowMapper<ApplyCustoms> rm = new BeanPropertyRowMapper<ApplyCustoms>(ApplyCustoms.class);
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.customer_id");
    sql.append(",a.customer,a.customs_broker,a.bill_of_lading,a.invoice_file,a.package_file,a.custom_file");
    sql.append(",a.contract_file,a.where_apply,a.release_permit,a.customs_date,a.create_time,a.create_user,a.update_time");
    sql.append(",a.update_user");
    sql.append(",a.payable,a.receivable");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_apply_customs a ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=0 and a.id=:id ");
    map.put("id", applyCustoms.getId());
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public Integer save(ApplyCustoms applyCustoms, User loginUser) {
    GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    Map<String, String> map  = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("insert into chf_apply_customs (");
    sql.append("organization_id,customer_id,customer,customs_broker,bill_of_lading,invoice_file,payable,receivable");
    sql.append(",package_file,custom_file,contract_file,release_permit,where_apply,customs_date,create_user,create_time) values ( ");
    sql.append(":organization_id,:customer_id,:customer,:customs_broker,:bill_of_lading,:invoice_file,:payable,:receivable");
    sql.append(",:package_file,:custom_file,:contract_file,:release_permit,:where_apply,:customs_date,:create_user,now())");
    map.put("organization_id", loginUser.getOrganizationId() + "");
    map.put("customer_id", applyCustoms.getCustomerId() == null ? null : applyCustoms.getCustomerId() + "");
    map.put("customer", applyCustoms.getCustomer());
    map.put("customs_broker", applyCustoms.getCustomsBroker());
    map.put("bill_of_lading", applyCustoms.getBillOfLading());
    map.put("invoice_file", applyCustoms.getInvoiceFile());
    map.put("package_file", applyCustoms.getPackageFile());
    map.put("custom_file", applyCustoms.getCustomFile());
    map.put("payable", applyCustoms.getPayable()+"");
    map.put("receivable", applyCustoms.getReceivable()+"");
    map.put("contract_file", applyCustoms.getContractFile());
    map.put("release_permit", applyCustoms.getReleasePermit());
    map.put("where_apply", applyCustoms.getWhereApply());
    map.put("customs_date", applyCustoms.getCustomsDate());
    map.put("create_user", loginUser.getUserId() + "");
    getMapParameter(sql, map);
    int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
      @Override
      public boolean hasValue(String paramName) {
        return map.containsKey(paramName);
      }
      @Override
      public Object getValue(String paramName) throws IllegalArgumentException {
        return map.get(paramName);
      }
    }, generatedKeyHolder);
    Integer id = generatedKeyHolder.getKey().intValue();
    return id;
  }

  @Override
  public boolean release(ApplyCustoms applyCustoms, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_apply_customs set ");
    sql.append("custom_file=:custom_file,release_permit=:release_permit ");
    sql.append(",update_time=now(),update_user=:update_user where id=:id ");
    map.put("custom_file", applyCustoms.getCustomFile());
    map.put("release_permit", applyCustoms.getReleasePermit());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", applyCustoms.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean update(ApplyCustoms applyCustoms, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_apply_customs set ");
    sql.append("customer_id=:customer_id,customer=:customer,customs_broker=:customs_broker,bill_of_lading=:bill_of_lading,invoice_file=:invoice_file ");
    sql.append(",package_file=:package_file,custom_file=:custom_file,contract_file=:contract_file,release_permit=:release_permit,where_apply=:where_apply ");
    sql.append(",payable=:payable,receivable=:receivable,customs_date=:customs_date");
    sql.append(",update_time=now(),update_user=:update_user where id=:id ");
    map.put("customer_id", applyCustoms.getCustomerId() == null ? null : applyCustoms.getCustomerId() + "");
    map.put("customer", applyCustoms.getCustomer());
    map.put("customs_broker", applyCustoms.getCustomsBroker());
    map.put("bill_of_lading", applyCustoms.getBillOfLading());
    map.put("invoice_file", applyCustoms.getInvoiceFile());
    map.put("package_file", applyCustoms.getPackageFile());
    map.put("custom_file", applyCustoms.getCustomFile());
    map.put("payable", applyCustoms.getPayable()+"");
    map.put("receivable", applyCustoms.getReceivable()+"");
    map.put("contract_file", applyCustoms.getContractFile());
    map.put("where_apply", applyCustoms.getWhereApply());
    map.put("release_permit", applyCustoms.getReleasePermit());
    map.put("customs_date", applyCustoms.getCustomsDate());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", applyCustoms.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean remove(ApplyCustoms applyCustoms, User loginUser) {
    int count = 0;
    Map<String, String> map = null;
    if (applyCustoms.getId().length() > 0) {
      for (String ID : applyCustoms.getId().split(",")) {
        map = new HashMap<String, String>();
        if (ID == null || ID.equals("")) {
          break;
        }
        StringBuffer sql = new StringBuffer("update chf_apply_customs set deleted=1" +
                ",update_user=:update_user, update_time=now() "
                + " where id =:id ");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", ID);
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(), map);
      }
    }
    return count > 0;
  }
}
